﻿#!/usr/bin/python
# -*- coding: utf-8 -*-

# mssql_data_dictionary.py

# first an ODBC connection to MSSQL database server has to be setup
# http://www.time-travellers.org/shane/howtos/MS-SQL-Express-Python-HOWTO.html
#  section Create the ODBC Data Source

#import odbc
import pyodbc
#print pyodbc.dataSources()
import sys
import re
import time
import string
import codecs

dtnow_iso = lambda : time.strftime ("%Y-%m-%dT%H%M%S", time.localtime())
#2008-07-16T19:44:18
#print dtnow_iso()

sei = lambda x: string.join([str(a) for a in x], '^')

def prep_out(input_dictionary):
    ''' prepare output string from input dictionary before writing to file output
    '''
    r = input_dictionary.copy()
    #print input_dictionary
    try:
        o = string.join([r['db'], r['schema'], r['table'], unicode(r['column']), 
                         r['c_type'], str(r['c_len']), str(r['c_precision']), 
                         unicode(r['c_scale']), unicode(r['q_result']), r['error'], 
                         sei(r['error_detail']), r['odbc_conn'],
                         dtnow_iso()], '\t') + '\x0D\x0A'
    except:
        o = string.join([r['db'], r['schema'], r['table'], unicode(r['column']), 
                         r['c_type'], str(r['c_len']), str(r['c_precision']), 
                         unicode(r['c_scale']), '', 'prep output error', 
                         str(input_dictionary), r['odbc_conn'],
                         dtnow_iso()], '\t') + '\x0D\x0A'
    return o

#def main(odbc_conn_name='dedus9009', search_for='Cyrill_LHGL_1'):
def main(odbc_conn_name='dedus9009', search_for=''):
    """ """
    #odbc_conn_name = 'dedus9009'
    #odbc_conn_name = 'localmssql'

    r = {   'db': '', 'schema': '', 'table':'', 'column': '',
            'c_type': '', 'c_len': '', 'c_precision': '', 'c_scale': '', 
            'q_result': '', 'error':'', 'error_detail': '', 'odbc_conn':''}
    r['odbc_conn'] = odbc_conn_name

    #conn = pyodbc.connect(dsn=odbc_conn_name)
    conn = pyodbc.connect("DSN=%s" % (odbc_conn_name,))
    cursor=conn.cursor()
    
    cursor.execute("SELECT * FROM SYS.DATABASES")
    databases = cursor.fetchall()[:]
    
    o = codecs.open('mssql_data_dictionary_%s.log'  % (dtnow_iso()), 'wb', 'utf-8')

    o.write(string.join(['DATABASE', 'SCHEMA', 'TABLE', 'COLUMN', 'C_TYPE', 'C_LEN',
                          'C_PRECISION', 'C_SCALE', 'Q_RESULT', 'ERROR', 'ERROR_DETAIL',
                          'ODBC_CONN', 'DATE_TIME',
                          '\x0D\x0A'], '\t'))

    for dbx in databases:

        # if investigating (searching for) only some db, tables, columns
        # default '' will match any db, table or column
        if      re.search(search_for, dbx[0]) == None:
            pass
        else:

            r = {   'db': '', 'schema': '', 'table': '', 'column': '',
                    'c_type': '', 'c_len': '', 'c_precision': '', 'c_scale': '', 
                    'q_result': '', 'error': '', 'error_detail': '', 'odbc_conn': ''}
            r['db'] = dbx[0]
            r['odbc_conn'] = odbc_conn_name
    
            r['db'] = dbx[0] # database name
            print r['db']
        
            if r['db'] in (      'master', 'model', 'backupmediaset'
                                , 'backupmediafamily''backupset', 'backupfile'
                                , 'restorehistory', 'restorefile', 'restorefilegroup'
                                , 'logmarkhistory', 'suspect_pages', 'msdb', 'tempdb'
                                ) :
                r['error'] = 'skipped db'
                r['error_detail'] = ''
                #print r
    
                o.write(prep_out(r))
                r = {   'db': '', 'schema': '', 'table':'', 'column': '',
                        'c_type': '', 'c_len': '', 'c_precision': '', 'c_scale': '', 
                        'q_result': '', 'error': '', 'error_detail': '', 'odbc_conn': ''}
                r['db'] = dbx[0]
                r['odbc_conn'] = odbc_conn_name
    
            else:
                try:
                    # select database
                    q = "USE %s" % (r['db'])
                    cursor.execute(q)
    
                    # get all columns information for given database
                    #  courtesy of http://www.dotnetspider.com/resources/20367-Get-list-all-columns-from-all-tables-SQL.aspx
                    #  enhanced with table schema information
                    q = """ select  s.name as [TableSchema],
                                    t.name as [TableName], 
                                    c.name as [Column_Name], 
                                    x.name as [Column_Type], 
                                    c.max_length as [Column_Len],
                                    c.precision as [Column_Precision], 
                                    c.scale as [Column_Scale]
                            from    sys.schemas s
                                join sys.tables t
                                    on s.schema_id = t.schema_id
                                left join sys.columns c
                                    on t.object_id = c.object_id
                                left join sys.types x
                                    on c.system_type_id = x.system_type_id  
                                    and c.user_type_id = x.user_type_id
                            order by 1, 2, 3
                            """
                    #q = q.encode('utf-8')
                    cursor.execute(q)
                    columns = cursor.fetchall()[:]
    
                    #o.write(str(columns[:]) + '\n0D\x0A')
                    for c in columns:
                        #print '\t column: ', c
                        #r['tb'] = "%s.[%s]" % (t[1], t[2]) # table name
                        r['schema']     = c[0] # table schema
                        r['table']      = c[1]
                        r['column']     = c[2]
                        r['c_type']     = c[3]
                        r['c_len']      = c[4]
                        r['c_precision'] = c[5]
                        r['c_scale']    = c[6]
    
                        # if investigating (searching for) only some db, tables, columns
                        # default '' will match any db, table or column
                        if      re.search(search_for, r['db']) == None \
                            and re.search(search_for, r['table']) == None \
                            and re.search(search_for, r['column']) == None:
                            pass
                        else:
                            #print r['table']
                            if r['table'] in ('sysdiagrams'):
                                pass
                                '''
                                elif r['db'] + r['schema'] + r['table']  not in \
                                    ('Cyrill_PARUSimportVendor_Customer_Mastertable'):
                                    # Cyrill_PARUS	import	Vendor_Customer_Mastertable	????/????????
                                    pass
                                '''
                            else:
                                try:
                                    #q = "SELECT count(*) FROM %s" % (r['tb'])
                                    q = """select top 1 [%s] from [%s].[%s] WITH (NOLOCK)
                                    where [%s] is not NULL and len([%s]) > 1""" % (r['column'], 
                                    r['schema'], r['table'], r['column'], r['column'])
                                    #print 'use', r['db'], '\ngo\n', q
                                    cursor.execute(q)
                                    result = cursor.fetchall()[:]
                                    #print result
                                
                                    if result == []:
                                        r['q_result'] = ''
                                    else:
                                        r['q_result'] = result[0][0]
                                        #print result[0][0]
            
                                    r['error'] = 'ok'
                                    r['error_detail'] = ''
        
                                    o.write(prep_out(r))
                                    # clear out r
                                    r = {   'db': '', 'schema': '', 'table': '', 'column': '',
                                            'c_type': '', 'c_len': '', 'c_precision': '', 'c_scale': '', 
                                            'q_result': '', 'error': '', 'error_detail': '', 'odbc_conn': ''}
                                    r['db'] = dbx[0]
                                    r['odbc_conn'] = odbc_conn_name
        
                                except:
                                    #print 'error:', r['tb']
                                    r['error'] = 'error'
                                    r['error_detail'] = sys.exc_info()[:]
                                    print sys.exc_info()
        
                                    o.write(prep_out(r))
                                    # clear out r
                                    r = {   'db': '', 'schema': '', 'table': '', 'column': '',
                                            'c_type': '', 'c_len': '', 'c_precision': '', 'c_scale': '', 
                                            'q_result': '', 'error': '', 'error_detail': '', 'odbc_conn': ''}
                                    r['db'] = dbx[0]
                                    r['odbc_conn'] = odbc_conn_name
    
                except:
                    r['error'] = 'error db'
                    r['error_detail'] = sys.exc_info()[:]
    
                    o.write(prep_out(r))
                    # clear out r
                    r = {   'db': '', 'schema': '', 'table':'', 'column': '',
                            'c_type': '', 'c_len': '', 'c_precision': '', 'c_scale': '', 
                            'q_result': '', 'error': '', 'error_detail': '', 'odbc_conn': ''}
                    r['db'] = dbx[0]
                    r['odbc_conn'] = odbc_conn_name
    
    o.close()

main()

if __name__ == "__main__":
    ''' '''
    pass
    '''
    if  len(sys.argv) == 2:
        main(sys.argv[1])
        #pass

    elif  len(sys.argv) == 3:
        main(sys.argv[1], sys.argv[2])

    else:
        print """

        mssql_data_dictionary.py <odbc_conn_name>

        Example :   mssql_data_dictionary some_odbc_conn Vendor

        Options:

        odbc_conn_name (mandatory)
            odbc connection name (target database server)
            
        search_for (optional)
            regular expression to search for db, table or column
        """
    '''